﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Caching;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using NGuestBook.Utility;

namespace NGuestBook.AccessDAL
{
    /**//// <summary>
    /// Access数据库操作助手
    /// </summary>
    public sealed class AccessDALHelper
    {
        /**//// <summary>
        /// 读取Access数据库的连接字符串
        /// 首先从缓存里读取，如果不存在则到配置文件中读取，并放入缓存
        /// </summary>
        /// <returns>Access数据库的连接字符串</returns>
        private static string GetConnectionString()
        {
            if (CacheAccess.GetFromCache("AccessConnectionString") != null)
            {
                return CacheAccess.GetFromCache("AccessConnectionString").ToString();
            }
            else
            {
                string dbPath = ConfigurationManager.AppSettings["AccessPath"];
                string dbAbsolutePath = HttpContext.Current.Server.MapPath(dbPath);
                string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];

                CacheDependency fileDependency = new CacheDependency(HttpContext.Current.Server.MapPath("Web.Config"));
                CacheAccess.SaveToCache("AccessConnectionString", connectionString.Replace("{DBPath}", dbAbsolutePath), fileDependency);

                return connectionString.Replace("{DBPath}", dbAbsolutePath);
            }
        }

        /**//// <summary>
        /// 执行SQL语句并且不返回任何值
        /// </summary>
        /// <param name="SQLCommand">所执行的SQL命令</param>
        /// <param name="parameters">参数集合</param>
        public static void ExecuteSQLNonQuery(string SQLCommand, OleDbParameter[] parameters)
        {
            OleDbConnection connection = new OleDbConnection(GetConnectionString());
            OleDbCommand command = new OleDbCommand(SQLCommand, connection);

            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        /**//// <summary>
        /// 执行SQL语句并返回包含查询结果的DataReader
        /// </summary>
        /// <param name="SQLCommand">所执行的SQL命令</param>
        /// <param name="parameters">参数集合</param>
        /// <returns></returns>
        public static OleDbDataReader ExecuteSQLDataReader(string SQLCommand, OleDbParameter[] parameters)
        {
            OleDbConnection connection = new OleDbConnection(GetConnectionString());
            OleDbCommand command = new OleDbCommand(SQLCommand, connection);

            for (int i = 0; i < parameters.Length; i++)
            {
                command.Parameters.Add(parameters[i]);
            }

            connection.Open();
            OleDbDataReader dataReader = command.ExecuteReader();
            //connection.Close();

            return dataReader;
        }

        /**//// <summary>
        /// 执行不需要返回数据的操作
        /// </summary>
        /// <param name="SQLCommand">SQL命令</param>
        /// <param name="parameters">参数</param>
        /// <returns>是否成功</returns>
        public static bool OperateNonData(string SQLCommand, OleDbParameter[] parameters)
        {
            try
            {
                ExecuteSQLNonQuery(SQLCommand, parameters);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /**//// <summary>
        /// 执行返回单个实体类的操作
        /// </summary>
        /// <typeparam name="T">实体类类型</typeparam>
        /// <param name="SQLCommand">SQL命令</param>
        /// <param name="parameters">参数</param>
        /// <param name="strategy">DataReader到实体类的转换策略</param>
        /// <returns>实体类</returns>
        public static T OperateEntity<T>(string SQLCommand, OleDbParameter[] parameters, IDataReaderToEntityStrategy<T> strategy)
        {
            OleDbDataReader dataReader = ExecuteSQLDataReader(SQLCommand, parameters);
            try
            {
                if (!dataReader.HasRows)
                {
                    throw new Exception();
                }

                dataReader.Read();
                return strategy.DataReaderToEntity(dataReader);
            }
            catch
            {
                return default(T);
            }
            finally
            {
                dataReader.Close();
            }
        }

        /**//// <summary>
        /// 执行返回实体类集合的操作
        /// </summary>
        /// <typeparam name="T">实体类类型</typeparam>
        /// <param name="SQLCommand">SQL命令</param>
        /// <param name="parameters">参数</param>
        /// <param name="strategy">DataReader到实体类的转换策略</param>
        /// <returns>实体类</returns>
        public static IList<T> OperateEntityCollection<T>(string SQLCommand, OleDbParameter[] parameters, IDataReaderToEntityStrategy<T> strategy)
        {
            OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, null);
            try
            {
                if (!dataReader.HasRows)
                {
                    throw new Exception();
                }

                IList<T> entityCollection = new List<T>();
                int i = 0;
                while (dataReader.Read())
                {
                    entityCollection.Add(strategy.DataReaderToEntity(dataReader));
                    i++;
                }

                return entityCollection;
            }
            catch
            {
                return default(IList<T>);
            }
            finally
            {
                dataReader.Close();
            }
        }
    }
}